/**************************************************************************
 *   Copyright (C) 2006 - 2007, AdCertify, Inc.  All Rights Reserved.
 *
 *   This program is an unpublished copyrighted work which is proprietary
 *   to AdCertify, Inc. and contains confidential information that is not
 *   to be reproduced or disclosed to any other person or entity without
 *   prior written consent from AdCertify, Inc. in each and every instance.
 *
 *   Unauthorized reproduction of this program as well as
 *   unauthorized preparation of derivative works based upon the
 *   program or distribution of copies by sale, rental, lease or
 *   lending are violations of Federal copyright laws and state trade
 *   secret laws, punishable by civil and criminal penalties.
 ***************************************************************************/

package com.wd.dao;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import snaq.db.ConnectionPool;
import snaq.db.ConnectionPoolManager;

import com.wd.shared.Constants;



/**
 * use DBPool to access db.The example is: public void foo(){ Connection con =
 * null; PreparedStatement pstmt = null; ResultSet rs = null; try{ con =
 * DAO.getInstance().getConnection(); pstmt = con.prepareStatement(...); rs =
 * pstmt.executeQuery(); ....... }catch(SQLException e){ ....... }finally{
 * DAO.close(rs); DAO.close(pstmt); DAO.close(con); } } modified by leo 20070730 
 * @author jerome
 * @version 1.0, 2009-9-7

 */
public class DAO {
	private static Log logger = LogFactory.getLog(DAO.class);

  // the connection of db, current use single connection, and for the long run
  // we will use connection pool.
  private static DAO instance = null;

  private static ConnectionPoolManager poolManager = null;

  public static ConnectionPool pool = null;

  private static String poolName = "dc";

  private static int initalCount = 10;

  private static String dbConfigFile;
  /**
   * Contructor
   */
  private DAO() {
    try {
      dbConfigFile = Constants.CONFIG_DBPOOL_PROPERTIES;
      init();
    } catch (Exception e) {
      logger.error(e);
      shutdown("");
    }
  }

  private static void testConnection() {
    try {
      Connection con = pool.getConnection();
      Statement st = con.createStatement();
      st.executeQuery("select 1");
      close(st);
      close(con);
      logger.info("Connection Pool init Successfully");
    } catch (Exception e) {
      logger.debug(e);
    }
  }

  /**
   * Contructor
   */
  private DAO(String dbconfig, String paramPoolName) {
    try {
      dbConfigFile = dbconfig;
      poolName = paramPoolName;
      init();
    } catch (Exception e) {
      logger.error(e);
    }
  }

  public static void setDBConfig(String dbconfig) {
    dbConfigFile = dbconfig;
  }

  /**
   * Singleton of DAO
   */
  public synchronized static DAO getInstance() {
    if (instance == null) {
      instance = new DAO();
    }
    return instance;
  }

  /**
   * Singleton of DAO
   */
  public synchronized static DAO getInstance(String dbconfig, String poolName) {
    if (instance == null) {
      instance = new DAO(dbconfig, poolName);
    }
    return instance;
  }

  /**
   * Get the Mysql connection.
   * 
   * @return Connection
   */
  public Connection getConnection(){
    Connection con = null;

    try {
      con = pool.getConnection();
      // con.setAutoCommit(false);
    } catch (SQLException sqle) {
      logger.error(dbConfigFile, sqle);
      con = null;
    }
    if (con == null) {
    	logger.info("Connection is failing ... ");
    }
    return con;
  }

  /**
   * setup dbcp database connection pool
   * 
   * @param driver
   * @param url
   * @param user
   * @param password
   * @return
   */
  public static void init() {
    try {
      poolManager = ConnectionPoolManager.getInstance(new File(dbConfigFile));
      pool = poolManager.getPool(poolName);
      pool.init(initalCount);
      testConnection();
    } catch (IOException e) {
      e.printStackTrace();
      logger.error(dbConfigFile, e);
      shutdown(dbConfigFile);
    }
  }

  public static void shutdown() {
    if (poolManager != null)
      poolManager.release();
  }

  /**
   * close ResultSet
   * 
   * @param rs
   */
  public static void close(ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (Exception e) {
        logger.warn(rs, e);
      }
    }
  }

  /**
   * close Statement
   * 
   * @param st
   */
  public static void close(Statement st) {
    if (st != null) {
      try {
        st.close();
      } catch (Exception e) {
        logger.warn(st, e);
      }
    }
  }

  /**
   * close connection
   * 
   * @param con
   */
  public static void close(Connection con) {
    try {
      if (con != null && !con.isClosed()) {
        con.close();
      }
    } catch (Exception e) {
      logger.warn(con, e);
    }
  }

  public static void close(Connection con, Statement st, ResultSet rs) {
    DAO.close(rs);
    DAO.close(st);
    DAO.close(con);
  }

  public static void close(Connection con, Statement st) {
    DAO.close(st);
    DAO.close(con);
  }

  public static List<String> executeQuery(String sql) {
    Connection con = null;
    ResultSet rs = null;
    Statement st = null;
    if (logger.isDebugEnabled()) {
      logger.debug(sql);
    }
    try {
      con = DAO.getInstance().getConnection();
      st = con.createStatement();
      rs = st.executeQuery(sql);
      List<String> list = new ArrayList<String>();
      while (rs.next()) {
        list.add(rs.getString(1));
      }
      return list;
    } catch (Exception e) {
      logger.error(sql, e);
    } finally {
      DAO.close(con, st, rs);
    }
    return null;
  }

  public static Object executeQuery(String sql, int type) {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    if (logger.isDebugEnabled()) {
      logger.debug(sql);
    }
    try {
      con = DAO.getInstance().getConnection();
      st = con.createStatement();
      rs = st.executeQuery(sql);
      Object o = null;
      if (rs.next()) {
        switch (type) {
        case java.sql.Types.INTEGER:
          int res = rs.getInt(1);
          o = new Integer(res);
          break;
        case java.sql.Types.FLOAT:
          float fRes = rs.getFloat(1);
          o = new Float(fRes);
          break;
        case java.sql.Types.VARCHAR:
          o = rs.getString(1);
          break;
        case java.sql.Types.DOUBLE:
          o = rs.getDouble(1);
          break;
        case java.sql.Types.VARBINARY:
          InputStream is = rs.getBinaryStream(1);
          ByteArrayOutputStream bos = new ByteArrayOutputStream();
          byte[] buf = new byte[1024];
          int len = 0;
          while ((len = is.read(buf)) > 0) {
            bos.write(buf, 0, len);
          }
          is.close();
          o = bos;
        }
      }
      return o;
    } catch (Exception e) {
      logger.error(sql, e);
    } finally {
      DAO.close(con, st, rs);
    }
    return null;
  }

  public static List<Object> execQuery(String sql, int type) {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    if (logger.isDebugEnabled()) {
      logger.debug(sql);
    }
    try {
      con = DAO.getInstance().getConnection();
      st = con.createStatement();
      rs = st.executeQuery(sql);
      List<Object> list = new ArrayList<Object>();
      while (rs.next()) {
        switch (type) {
        case java.sql.Types.INTEGER:
          int res = rs.getInt(1);
          list.add(new Integer(res));
          break;
        case java.sql.Types.FLOAT:
          float fRes = rs.getFloat(1);
          list.add(new Float(fRes));
          break;
        case java.sql.Types.VARCHAR:
          list.add(rs.getString(1));
          break;
        case java.sql.Types.VARBINARY:
          byte[] ba = rs.getBytes(1);
          list.add(ba);
        }
      }
      return list;
    } catch (Exception e) {
      logger.error(sql, e);
    } finally {
      DAO.close(con, st, rs);
    }
    return null;
  }

  // public static ResultSet execQuery(String sql){
  // Connection con = null;
  // Statement st = null;
  // ResultSet rs = null;
  //
  // logger.debug(sql);
  // try{
  // con = DAO.getInstance().getConnection();
  // st = con.createStatement();
  // rs =st.executeQuery(sql);
  // }catch(Exception e){
  // logger.error(sql,e);
  // DAO.close(con,st,rs);
  // }
  // return rs;
  // }

  // public static void closeAll(ResultSet rs){
  // if (rs != null) {
  // Statement st = null;
  // Connection con = null;
  // try{
  // st = rs.getStatement();
  // if(st != null){
  // con = st.getConnection();
  // }
  // }catch(Exception e){
  // logger.warn(rs,e);
  // }
  // DAO.close(con,st,rs);
  // }
  // }
  //
  public static Timestamp executeTimestampQuery(String sql) {
    Connection con = null;
    // PreparedStatement pstmt = null;
    Statement st = null;
    ResultSet rs = null;
    if (logger.isDebugEnabled()) {
      logger.debug(sql);
    }
    try {
      con = DAO.getInstance().getConnection();
      // pstmt = con.prepareStatement(sql);
      st = con.createStatement();
      rs = st.executeQuery(sql);
      // rs = pstmt.executeQuery();
      if (rs.next()) {
        return rs.getTimestamp(1);
      }
    } catch (Exception e) {
      logger.error(sql, e);
    } finally {
      DAO.close(con, st, rs);
    }
    return null;
  }

  public static int executeUpdate(String sql) {
    if (logger.isDebugEnabled()) {
      logger.debug(sql);
    }
    Connection con = null;
    Statement st = null;
    int res = 0;
    try {
      con = DAO.getInstance().getConnection();
      st = con.createStatement();
      res = st.executeUpdate(sql);
    } catch (Exception e) {
      logger.error(sql, e);
    } finally {
      DAO.close(con, st, null);
    }
    return res;
  }

  public static long executeInsert(String sql) {
    if (logger.isDebugEnabled()) {
      logger.debug(sql);
    }

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    int res = -1;
    try {
      con = DAO.getInstance().getConnection();
      st = con.createStatement();
      st.executeUpdate(sql);
      rs = st.getGeneratedKeys();
      if (rs != null && rs.next()) {
        long id = rs.getLong(1);
        return id;
      }
    } catch (Exception e) {
      logger.error(sql, e);
    } finally {
      DAO.close(con, st, rs);
    }
    return res;
  }

  public static String filter(String s) {
    if (s == null || "".equals(s.trim())) {
      return null;
    } else {
      if (s.indexOf('\\') >= 0) {
        s = s.replace("\\", "\\\\");
      }
      if (s.indexOf('\'') >= 0) {
        s = s.replace("'", "''");
      }
      s = '\'' + s.trim() + '\'';
      return s;
    }
  }

  private static void shutdown(String msg) {
    logger.fatal(msg);
    System.exit(0);
  }

  public static void main(String args[]) {
    SimpleDateFormat sdf = new SimpleDateFormat("K:mm a, z");
    String day = sdf.format(new Timestamp(System.currentTimeMillis()));

    try {
      System.err.println(day);
    } catch (Exception e) {
      logger.error(e);
    }
  }
}
